﻿/*
#------------------------------------------------------------------------------
#-- Program Name:	[dbo].[spDBA_purge_unnecessary_performance_counters]
#-- Purpose:		The default Data Collector captures far too many performance
#--					counters for routine usage.  This stored procedure removes
#--					all Process counters that are not in the top 20 of their
#--					category - leaving plenty for troubleshooting.
#--	Last Update:	09/07/2011
#--					For a complete history - please review comments in Version
#--					Control.
#------------------------------------------------------------------------------
*/
CREATE PROCEDURE [dbo].[spDBA_purge_unnecessary_performance_counters]
(
	@start_date			datetime			= NULL,
	@end_date			datetime			= NULL,
	@keep_count			int					= 20
)
AS

SET NOCOUNT ON

/*
--- If no dates were passed, default to today
SELECT	@start_date = DATEADD(DAY, 0, DATEDIFF(DAY, 0, GETDATE())),
		@end_date = DATEADD(dd, 1, @start_date)

--- Remove the > top @keep_count records
;WITH snap AS 
(
	SELECT		DISTINCT
				src.instance_name,
				time.snapshot_time_id,
				snap.snapshot_id,
				time.snapshot_time
	FROM		DataCollectionWarehouse.core.snapshots_internal snap
	JOIN		DataCollectionWarehouse.core.snapshot_timetable_internal time ON snap.snapshot_time_id = time.snapshot_time_id
	JOIN		DataCollectionWarehouse.core.source_info_internal src ON snap.source_id = src.source_id
	WHERE		EXISTS	(
						SELECT		TOP 1 *
						FROM		DataCollectionWarehouse.snapshots.performance_counter_instances inst
						JOIN		DataCollectionWarehouse.snapshots.performance_counter_values val ON inst.performance_counter_id = val.performance_counter_instance_id
									AND val.snapshot_id = snap.snapshot_id
									AND inst.object_name = 'Process'
									AND inst.instance_name <> '_Total'
						)
),
perf AS
(
	SELECT		val.performance_counter_instance_id,
				snap.snapshot_id,
				snap.snapshot_time_id,
				snap.instance_name server,
				val.collection_time,
				inst.object_name,
				inst.counter_name,
				inst.instance_name,
				ROW_NUMBER() OVER(PARTITION BY	snap.snapshot_id,
												snap.snapshot_time_id,
												snap.instance_name,
												val.collection_time,
												inst.object_name,
												inst.counter_name
								  ORDER BY		snap.snapshot_id,
												snap.snapshot_time_id,
												snap.instance_name,
												val.collection_time,
												inst.object_name,
												inst.counter_name,
												val.formatted_value DESC ) ranking,
				val.formatted_value
	FROM		snap
	JOIN		DataCollectionWarehouse.snapshots.performance_counter_values val ON snap.snapshot_id = val.snapshot_id
	JOIN		DataCollectionWarehouse.snapshots.performance_counter_instances inst ON val.performance_counter_instance_id = inst.performance_counter_id
				AND inst.object_name = 'Process'
				AND inst.instance_name <> '_Total'
				AND inst.instance_name <> 'Idle'
	WHERE		snap.snapshot_time BETWEEN @start_date AND @end_date
)
DELETE		val
FROM		perf
JOIN		DataCollectionWarehouse.snapshots.performance_counter_values val ON val.performance_counter_instance_id = perf.performance_counter_instance_id
			AND val.snapshot_id = perf.snapshot_id
			AND val.collection_time = perf.collection_time
WHERE		perf.ranking > @keep_count
*/

SET NOCOUNT OFF